<?php
/**
 * Created by PhpStorm.
 * User: Mcat
 * Date: 2019/5/9
 * Time: 17:32
 */
namespace app\common\service\week;

use app\common\service\BaseService;
use think\Db;

class Week
{
    
    /**
     * 系统配置参数信息
     * @author Mcat
     * @param $region_id
     * @return mixed
     */
    public function getSetting($region_id)
    {
        $setting_condition = [$region_id];
        $setting_data      = Db::query("select `name_en`, `value` from `system_setting` where region_id = ?", $setting_condition);
        return $setting_data;
    }
    
    /**
     * 气象信息 —— 发布时间 最高气温 最低气温 最高风力 风向 最高湿度 最高总云量对应的天空情况 最高总云量对应的天空情况对应的图标
     * @author Mcat
     * @param $region_id
     * @param $time_start
     * @param $time_end
     * @return mixed
     */
    public function getLastWeather($region_id, $time_start, $time_end)
    {
        $weather_condition      = [$region_id, $time_start, $time_end];
        $last_week_weather_data = Db::query("select `publish_time`,`maximum_temperature`, `minimum_temperature`, `highest_wind`, `wind_direction`, `maximum_humidity`, `maximum_sky_condition`, `maximum_sky_condition_icon` from `air_quality_weather_realtime_day` where `region_id`=? AND ( `publish_time` between ? AND ? )", $weather_condition);
        return $last_week_weather_data;
    }
    
    /**
     * AQI值 —— 发布时间 AQI值
     * @author Mcat
     * @param $region_id
     * @param $station_id
     * @param $time_start
     * @param $time_end
     * @return mixed
     */
    public function getLastAqi($region_id, $station_id, $time_start, $time_end)
    {
        $contaminant_condition      = [$region_id, $station_id, $time_start, $time_end];
        $last_week_contaminant_data = Db::query("select `publish_time`, `aqi` from `air_quality_station_aqi_realtime_day` where `region_id`=? AND `station_id`=? AND (`publish_time` between ? AND ? ) ", $contaminant_condition);
        return $last_week_contaminant_data;
    }
    
    /**
     * 获取上周——首要污染物的名称
     * @author Mcat
     * @param $region_id
     * @param $station_id
     * @param $time_start
     * @param $time_end
     * @return mixed
     */
    public function getLastPollutants($region_id, $station_id, $time_start, $time_end)
    {
        $api_condition      = [$region_id, $station_id, $time_start, $time_end];
        $last_week_aqi_data = Db::query("select `publish_time`, `name` from `air_quality_station_primary_pollutant_realtime_day`  where `region_id`=? AND `station_id`=? AND (`publish_time` between ? AND ? ) ", $api_condition);
        return $last_week_aqi_data;
    }
    
    /**
     * 获取上周——首要污染物
     * @author Mact
     * @param $region_id 区域ID 默认 53 长治市
     * @param $station_id 站点ID 默认 0 全市
     * @param $time_start 开始时间 默认 上周开始日期
     * @param $time_end 结束时间 默认 上周结束日期
     * @return mixed
     */
    public function getLastPrimaryPollutant($region_id, $station_id, $time_start, $time_end)
    {
        $primary_pollutant_condition = [$region_id, $station_id, $time_start, $time_end];
        $primary_pollutant_data      = Db::query("select `publish_time`, `name`, `value`  from `air_quality_station_primary_pollutant_realtime_day` where `region_id`=? AND `station_id`=? AND (`publish_time` between ? AND ? ) ", $primary_pollutant_condition);
        return $primary_pollutant_data;
    }
    
    /**
     * 获取上周——首要污染物出现小时
     * @author Mcat
     * @param $region_id 地区
     * @param $station_id 站点
     * @param $time_start 开始时间
     * @param $time_end 结束时间
     * @param $pollutant_name 污染物名称
     * @return mixed
     */
    public function getLastPrimaryPollutantHour($region_id, $station_id, $time_start, $time_end, $pollutant_name)
    {
        $primary_pollutant_condition = [$region_id, $station_id, $pollutant_name, $time_start, $time_end];
        $primary_pollutant_data      = Db::query("select `publish_time` from `air_quality_station_primary_pollutant_realtime_hour` where `region_id`=? AND `station_id`=? AND `name`=? AND (`publish_time` between ? AND ?)", $primary_pollutant_condition);
        return array_column($primary_pollutant_data, 'publish_time');
    }
    
    /**
     * 获取上周——指定站点、污染物 指定小时的浓度
     * @author Mcat
     * @param $region_id
     * @param $station_id
     * @param $time 对应小时
     * @param $pollutant_name 污染物名称
     * @return mixed
     */
    public function getLastSiteConcentrationHour($region_id, $station_id, $time, $pollutant_name)
    {
        // 将日期条件拼接为in条件需要格式
        $time_t = "";
        foreach ($time as $k => $v)
        {
            $time_t .= '\''.$v . '\'' . ',';
        }
        $time_t = substr($time_t, 1);
        $time_t = substr($time_t, 0, -2);
        // 将参数重组为sql条件
        $site_concentration_condition = [$region_id, $station_id];
        $site_concentration_data      = Db::query("select `$pollutant_name` from `air_quality_station_aqi_realtime_hour` where `region_id`=? AND `station_id`=? AND `publish_time` in ('".$time_t."')", $site_concentration_condition);
        // 将结果降维
        foreach ($site_concentration_data as $key => $val)
        {
            $site_concentration_data[$key] = $val[$pollutant_name];
        }
        return $site_concentration_data;
    }
    
    /**
     * 上周——获取指定污染物24h浓度值
     * @author Mcat
     * @param $region_id
     * @param $time_start
     * @param $time_end
     * @param $pollutant_name
     * @return mixed
     */
    public function getLastSiteConcentration24h($region_id, $time_start, $time_end, $pollutant_name)
    {
        if ($pollutant_name == 'O3_8H')
        {
            $pollutant_name = 'o3_8hour';
        }
        $site_concentration_condition = [$region_id, $time_start, $time_end];
        $site_concentration_data      = Db::query("SELECT `$pollutant_name`,`station_id`,`publish_time` as pt_time FROM `air_quality_station_aqi_realtime_hour` WHERE `region_id`=? AND `station_id` in ('1','2','3','4','5') AND (`publish_time` between ? AND ? )", $site_concentration_condition);
        $data = array();
        foreach ($site_concentration_data as $k => $v)
        {
            $data[$v['station_id']][$v['pt_time']] = $v[$pollutant_name];
        }
        return $data;
    }
    
    /**
     * 获取上周相关污染物
     * @author Mcat
     * @param $region_id 区域ID 默认 53 长治市
     * @param $station_id 站点ID 默认 0 全市
     * @param $time_start 开始时间 默认 上周开始日期
     * @param $time_end 结束时间 默认 上周结束日期
     * @return mixed
     */
    public function getLastRelatedPollutant($region_id, $station_id, $time_start, $time_end)
    {
        $relevance_pollutant_condition = [$region_id, $station_id, $time_start, $time_end];
        $relevance_pollutant_data      = Db::query("select `publish_time`, `relevance_name` from `air_quality_station_relevance_pollutant_realtime_day` where `region_id`=? AND `station_id`=? AND (`publish_time` between ? AND ? ) AND `pearson_value` >= 0.75", $relevance_pollutant_condition);
        return $relevance_pollutant_data;
    }
    
    /**
     * 上周获取相关污染物的浓度值
     * @author Mcat
     * @param $region_id
     * @param $station_id
     * @param $time_start
     * @param $time_end
     * @return mixed
     */
    public function getLastRelatedPollutantValue($region_id, $station_id, $time_start, $time_end)
    {
        $relevance_pollutant_val_condition = [$region_id, $station_id, $time_start, $time_end];
        $relevance_pollutant_val_data      = Db::query("select `publish_time`, `pm10`, `pm25`, `so2`, `no2`, `co`, `o3` from `air_quality_station_aqi_realtime_day`  where `region_id`=? AND `station_id`=? AND (`publish_time` between ? AND ? )", $relevance_pollutant_val_condition);
        return $relevance_pollutant_val_data;
    }
    
    
    /**
     * 获取本周首要污染物
     * @author mcat
     * @param $region_id 区域ID 默认 53 长治市
     * @param $station_id 站点ID 默认 0 全市
     * @param $time_start 今天的日期
     * @param $time_end 本周结束的日期
     * @return mixed
     */
    public function getThisPrimaryPollutant($region_id, $station_id, $time_start, $time_end)
    {
        $primary_pollutant_condition = [$region_id, $station_id, $time_start, $time_start, $time_end];
        $primary_pollutant_data      = Db::query("select `predict_time` as `publish_time`, `name`, `value`  from `air_quality_station_primary_pollutant_forecast_day` where `region_id`=? AND `station_id`=? AND `publish_time`=? AND (`predict_time` between ? AND ? )", $primary_pollutant_condition);
        return $primary_pollutant_data;
    }
    
    /**
     * 获取本周相关污染物
     * @param $region_id 区域ID 默认 53 长治市
     * @param $station_id 站点ID 默认 0 全市
     * @param $time_start 今天的日期
     * @param $time_end 本周结束的日期
     * @return mixed
     */
    public function getThisRelatedPollutant($region_id, $station_id, $time_start, $time_end)
    {
        $relevance_pollutant_condition = [$region_id, $station_id, $time_start, $time_start, $time_end];
        $relevance_pollutant_data      = Db::query("select `predict_time` as `publish_time`, `relevance_name` from `air_quality_station_relevance_pollutant_forecast_day` where `region_id`=? AND `station_id`=? AND `publish_time`=? AND (`predict_time` between ? AND ? ) AND `pearson_value` >= 0.75", $relevance_pollutant_condition);
        return $relevance_pollutant_data;
    }
    
    /**
     * 本周获取相关污染物的浓度值
     * @author Mcat
     * @param $region_id
     * @param $station_id
     * @param $time_start
     * @param $time_end
     * @return mixed
     */
    public function getThisRelatedPollutantValue($region_id, $station_id, $time_start, $time_end)
    {
        $relevance_pollutant_val_condition = [$region_id, $station_id, $time_start, $time_start, $time_end];
        $relevance_pollutant_val_data      = Db::query("select `predict_time` as `publish_time`, `pm10`, `pm25`, `so2`, `no2`, `co`, `o3` from `air_quality_station_aqi_forecast_day`  where `region_id`=? AND `station_id`=? AND `publish_time`=? AND (`predict_time` between ? AND ? )", $relevance_pollutant_val_condition);
        return $relevance_pollutant_val_data;
    }
    
    /**
     * 获取本周——首要污染物出现小时
     * @author Mcat
     * @param $region_id 地区
     * @param $station_id 站点
     * @param $time_start 开始时间
     * @param $time_end 结束时间
     * @param $pollutant_name 污染物名称
     * @return mixed
     */
    public function getThisPrimaryPollutantHour($region_id, $station_id, $time_start, $time_end, $pollutant_name)
    {
        $primary_pollutant_condition = [$region_id, $station_id, $pollutant_name, $time_start, $time_start, $time_end];
        $primary_pollutant_data      = Db::query("select `publish_time` from `air_quality_station_primary_pollutant_forecast_hour` where `region_id`=? AND `station_id`=? AND `name`=? AND `publish_time`=? AND (`predict_time` between ? AND ?)", $primary_pollutant_condition);
        return array_column($primary_pollutant_data, 'publish_time');
    }
    
    /**
     * 获取本周——指定站点、污染物 指定小时的浓度
     * @author Mcat
     * @param $region_id
     * @param $station_id
     * @param $time_start 今天
     * @param $time 对应小时
     * @param $pollutant_name 污染物名称
     * @return mixed
     */
    public function getThisSiteConcentrationHour($region_id, $station_id, $time_start, $time, $pollutant_name)
    {
        // 将日期条件拼接为in条件需要格式
        $time_t = "";
        foreach ($time as $k => $v)
        {
            $time_t .= '\''.$v . '\'' . ',';
        }
        $time_t = substr($time_t, 1);
        $time_t = substr($time_t, 0, -2);
        // 将参数重组为sql条件
        $site_concentration_condition = [$region_id, $station_id, $time_start];
        $site_concentration_data      = Db::query("select `$pollutant_name` from `air_quality_station_aqi_forecast_hour` where `region_id`=? AND `station_id`=? AND `publish_time`=? AND `predict_time` in ('".$time_t."')", $site_concentration_condition);
        // 将结果降维
        foreach ($site_concentration_data as $key => $val)
        {
            $site_concentration_data[$key] = $val[$pollutant_name];
        }
        return $site_concentration_data;
    }
    
    /**
     * 本周——获取指定污染物24h浓度值
     * @param $region_id        区域ID
     * @param $time_start       预报时间
     * @param $time_end         预报时间
     * @param $pollutant_name   污染物名称
     * @param $publish_time     发布时间
     * @return array
     */
    public function getThisSiteConcentration24h($region_id, $time_start, $time_end, $pollutant_name, $publish_time)
    {
        if ($pollutant_name == 'O3_8H')
        {
            $pollutant_name = 'o3_8hour';
        }
        $site_concentration_condition = [$region_id, $time_start, $time_end, $publish_time];
        $site_concentration_data      = Db::query("SELECT `$pollutant_name`,`station_id`,`predict_time` as pt_time FROM `air_quality_station_aqi_forecast_hour` WHERE `region_id`=? AND `station_id` in ('1','2','3','4','5') AND (`predict_time` between ? AND ? ) AND `publish_time`=?", $site_concentration_condition);
        $data = array();
        foreach ($site_concentration_data as $k => $v)
        {
            $data[$v['station_id']][$v['pt_time']] = $v[$pollutant_name];
        }
        return $data;
    }
    
}

